package com.freecycle.sg.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

import com.freecycle.sg.sqlite.Freebie.Freebies;
import com.freecycle.sg.sqlite.Freebie.ReferenceData;
import com.freecycle.sg.sqlite.Freebie.Users;

public class FreebieDatabase extends SQLiteOpenHelper {

	private static final String TAG = "FreebieDatabase";

	private static final String DATABASE_NAME = "freebies.db";
	
	private static final int DATABASE_VERSION = 1;

	/**
	 * Constructor of database helper class. This helper class will be in charge
	 * of Freebie database creation and upgrade.
	 **/
	public FreebieDatabase(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	public FreebieDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/** Enumerate all tables in FreebieDatabase */
	interface Tables {
		String REFERENCES = "reference_data";
		String USERS = "users";
		String FREEBIES = "freebies";
	}

	/** Perform database creation, if it's not existed */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.v(TAG, "Creating Freebie database " + db.toString());
		
		db.execSQL("CREATE TABLE " + Tables.REFERENCES + "("  
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ ReferenceData.REF_DATA_TYPE + " TEXT NOT NULL,"
				+ ReferenceData.ABBREVIATION + " TEXT,"
				+ ReferenceData.NAME + " TEXT," 
				+ ReferenceData.MARKER_URL + " TEXT)");

		db.execSQL("CREATE TABLE " + Tables.USERS + " (" 
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ Users.USER_ID + " TEXT NOT NULL,"
				+ Users.USER_PASSWORD + " TEXT," 
				+ Users.USER_NAME + " TEXT NOT NULL," 
				+ Users.USER_EMAIL + " TEXT NOT NULL,"
				+ Users.USER_PHONE + " TEXT,"
				+ Users.LATITUDE_USER + " TEXT," 
				+ Users.LONGITUDE_USER + " TEXT," 
				+ "UNIQUE (" + Users.USER_ID + ") ON CONFLICT REPLACE)");
		
		db.execSQL("CREATE TABLE " + Tables.FREEBIES + " (" 
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
				+ Freebies.FREEBIE_ID + " TEXT NOT NULL," 
				+ Freebies.ADDED + " TEXT,"
				+ Freebies.LATITUDE + " TEXT,"
				+ Freebies.LONGITUDE + " TEXT,"
				+ Freebies.FORMATTED_ADDRESS + " TEXT," 
				+ Freebies.NAME + " TEXT," 
				+ Freebies.TITLE + " TEXT," 
				+ Freebies.DESCRIPTION + " TEXT,"
				+ Freebies.CATEGORY + " TEXT NOT NULL,"
				+ Freebies.PHONE + " TEXT,"
				+ "UNIQUE (" + Freebies.FREEBIE_ID + ") ON CONFLICT REPLACE)");

	}

	/** Do database upgrade as necessary */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.v(TAG, "Upgrading database from " + oldVersion + " to " + newVersion);
		if (oldVersion != DATABASE_VERSION) {
			Log.v(TAG, "Destroying old data if exists");

			db.execSQL("DROP TABLE IF EXISTS " + Tables.REFERENCES);
			db.execSQL("DROP TABLE IF EXISTS " + Tables.USERS);
			db.execSQL("DROP TABLE IF EXISTS " + Tables.FREEBIES);

			onCreate(db);
		}
	}

}
